from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text

app = Flask(__name__)


# MYSQL所在的主机名
host = '127.0.0.1'
# MYSQL监听的端口号
port = 3306
# 连接mysql时的用户名
username = 'root'
# 密码
password = 'root'
# mysql上创建的数据库名称
database = 'database_learn'

app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{username}:{password}@{host}:{port}" \
                                        f"/{database}?charset=utf8mb4"
db = SQLAlchemy(app)


@app.route("/")
def hello():
    return "您好!"


# with app.app_context():
#     with db.engine.connect() as conn:
#         rs = conn.execute(text("select 1"))
#         print(rs.fetchone())

class User(db.Model):
    __tablename__ ='user'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    username = db.Column(db.String(100),nullable=False)
    password = db.Column(db.String(100),nullable=False)
    articles = db.relationship("Article",back_populates='author')
    extension = db.relationship('UserExtension',back_populates='user',uselist=False)

    def to_dict(self):
        return {
            'id':self.id,
            'username':self.username,
            'password':self.password
        }


class UserExtension(db.Model):
    """用户拓展模型"""
    __tablename__ = 'user_extension'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    school = db.Column(db.String(100))
    user_id = db.Column(db.Integer,db.ForeignKey("user.id"),unique=True)

    user = db.relationship('User',back_populates='extension')

    def to_dict(self):
        return {
            'id':self.id,
            'school':self.school,
            'user_id':self.user_id
        }


article_tag_table = db.Table(
    "article_tag_table",
    db.Column('article_id',db.Integer,db.ForeignKey("article.id"),primary_key=True),
    db.Column('tag_id',db.Integer,db.ForeignKey('tag.id'),primary_key=True)
)


class Article(db.Model):
    """文章类"""
    __tablename__='article'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    title = db.Column(db.String(200),nullable=False)
    content = db.Column(db.Text,nullable=False)

    author_id = db.Column(db.Integer,db.ForeignKey("user.id"))
    author = db.relationship("User",back_populates='articles')
    tags = db.relationship("Tag",secondary=article_tag_table,back_populates='articles')

    def to_dict(self):
        return {
            'id':self.id,
            'title':self.title,
            'content':self.content,
            'author_id':self.author_id
        }


class Tag(db.Model):
    __tablename__ = 'tag'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    name = db.Column(db.String(100))
    articles = db.relationship("Article",secondary=article_tag_table,back_populates='tags')

    def to_dict(self):
        return {
            'id':self.id,
            'name':self.name
        }


with app.app_context():
    db.create_all()


@app.route("/art")
def article_tags():
    article = Article.query.get(4)
    tag_list=[]
    for tag in article.tags:
        tag_list.append(tag.to_dict())
    return {"article":article.to_dict(),"tags":tag_list}


@app.route("/update/author")
def update_user():
    article = Article.query.get(4)
    article.author_id = 3
    db.session.add(article)
    db.session.commit()
    return "修改成功"

@app.route("/m2m")
def many2many():
    article1 = Article(title="11",content="aa")
    article2 = Article(title="22",content="bbbb")

    tag1 = Tag(name='python')
    tag2 = Tag(name="flask")

    article1.tags.append(tag1)
    article1.tags.append(tag2)

    article2.tags.append(tag1)
    article2.tags.append(tag2)

    db.session.add_all([article1,article2])
    db.session.commit()
    return "多对多数据添加成功"
@app.route("/o2o")
def one2one():
    user = User.query.first()
    extension1 = UserExtension(school='清华大学',user=user)

    db.session.add(extension1)
    db.session.commit()
    return "一对一成功!"


@app.route("/article/add")
def article_add():
    user = User.query.first()
    article = Article(title='aa',content='bb',author=user)
    db.session.add(article)
    db.session.commit()

    article = Article.query.filter_by(title="aa").first()
    print(article.author.username)
    return article.to_dict()


@app.route("/detail")
# 访问用户详细信息
def user_detail():
    user = User.query.first()
    print(f"{user.to_dict()}--{user.extension.to_dict()}")
    return {'user':user.to_dict(),'user_detail':user.extension.to_dict()}


@app.route("/user/article")
def get_user_article():
    user = User.query.first()
    data = []
    for article in user.articles:
        print(f"{article.title}--{article.content}")
        data.append(article.to_dict())
    return data


@app.route("/users")
def get_users():
    users = User.query.all()
    data = []
    for user in users:
        print(f"{user.id}--{user.username}--{user.password}")
        data.append(user.to_dict())
    return data


if __name__ == '__main__':
    app.run(port=8080,debug=True)